﻿using CK.Sprite.Form.Core;
using CK.Sprite.Framework;
using Dapper;
using Dapper.Contrib.Extensions;
using JetBrains.Annotations;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace CK.Sprite.Form.MySql
{
    public class MysqlRuntimeRepository : BaseSpriteRepository<Object>, IRuntimeRepository
    {
        public MysqlRuntimeRepository(IUnitOfWork unitOfWork) : base(unitOfWork) { }

        #region Sql

        private const string SqlDefaultCreate = "INSERT INTO `#TableName#`(#Fields#) VALUES (#Values#);";
        private const string SqlDefaultUpdate = "UPDATE `#TableName#` SET #FieldValues# WHERE Id=@Id #SqlWhere#;";
        private const string SqlDefaultDelete = "DELETE FROM `#TableName#` WHERE Id=@Id #SqlWhere#;";
        private const string SqlDefaultGet = "SELECT #Fields# FROM `#TableName#` WHERE Id=@Id #SqlWhere#;";
        private const string SqlDefaultList = "SELECT #Fields# FROM `#TableName#` WHERE 1=1 #SqlWhere##OrderBy#;";

        private const string SqlDefaultUpdateWhere = "UPDATE `#TableName#` SET #FieldValues# WHERE #SqlWhere#;";
        private const string SqlDefaultDeleteWhere = "DELETE FROM `#TableName#` WHERE #SqlWhere#;";
        private const string SqlDefaultGetWhere = "SELECT #Fields# FROM `#TableName#` WHERE #SqlWhere#;";
        private const string SqlDefaultListWhere = "SELECT #Fields# FROM `#TableName#` WHERE #SqlWhere##OrderBy#;";

        private const string SqlDefaultPageCount = "SELECT COUNT(1) FROM `#TableName#` WHERE #SqlWhere#";
        private const string SqlDefaultPageList = "SELECT #Fields# FROM `#TableName#` WHERE #SqlWhere##OrderBy# LIMIT #SkipCount#,#MaxResultCount#;";

        #endregion

        #region Default Method Call

        public async Task<JObject> DoDefaultCreateMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, string sqlMethodContent = "")
        {
            StringBuilder sbInsertFields = new StringBuilder();
            StringBuilder sbInsertValues = new StringBuilder();

            var newGuidId = Guid.NewGuid();
            if (spriteObjectDto.KeyType == EKeyType.Guid)
            {
                sbInsertFields.Append($"{MysqlConsts.PreMark}Id{MysqlConsts.PostMark},");
                sbInsertValues.Append($"'{newGuidId}',");
            }
            else
            {
                sbInsertFields.Append($"{MysqlConsts.PreMark}Id{MysqlConsts.PostMark},");
                sbInsertValues.Append($"0,");
            }

            foreach (var paramValue in paramValues)
            {
                var field = paramValue.Key;
                if (spriteObjectDto.ObjectPropertyDtos.Any(r => r.Name.ToLower() == field.ToLower()))
                {
                    sbInsertFields.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark},");
                    sbInsertValues.Append($"@{field},");
                }
            }

            var tempParamValues = paramValues.DeepClone().ToObject<JObject>();
            var nowTime = DateTime.Now;

            if (spriteObjectDto.IsTree)
            {
                CreateTree(sbInsertFields, sbInsertValues, spriteObjectDto, tempParamValues);
            }

            if (spriteObjectDto.CreateAudit)
            {
                CreateAuditCreate(sbInsertFields, sbInsertValues, nowTime, tempParamValues);
            }

            if (spriteObjectDto.ModifyAudit)
            {
                CreateAuditUpdate(sbInsertFields, sbInsertValues, nowTime, tempParamValues);
            }

            var strInserSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultCreate : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#Fields#", sbInsertFields.ToString().TrimEnd(','))
                .Replace("#Values#", sbInsertValues.ToString().TrimEnd(','));

            JObject result = new JObject();
            if (spriteObjectDto.KeyType == EKeyType.Guid)
            {
                await _unitOfWork.Connection.ExecuteAsync(strInserSql, tempParamValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", newGuidId));
            }
            else
            {
                var resultId = await _unitOfWork.Connection.QueryFirstAsync<int>(strInserSql + "SELECT LAST_INSERT_ID();", tempParamValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultId));
            }

            return result;
        }

        public async Task<JObject> DoDefaultUpdateMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, string sqlMethodContent = "")
        {
            StringBuilder sbUpdateFieldValues = new StringBuilder();

            foreach (var paramValue in paramValues)
            {
                var field = paramValue.Key;
                if (field != "Id" && spriteObjectDto.ObjectPropertyDtos.Any(r => r.Name.ToLower() == field.ToLower()))
                {
                    sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}=@{field},");
                }
            }

            var tempParamValues = paramValues.DeepClone().ToObject<JObject>();
            var nowTime = DateTime.Now;

            if (spriteObjectDto.IsTree)
            {
                UpdateTree(sbUpdateFieldValues, spriteObjectDto, tempParamValues);
            }

            if (spriteObjectDto.ModifyAudit)
            {
                UpdateAuditUpdate(sbUpdateFieldValues, nowTime, tempParamValues);
            }

            var strUpdateSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultUpdate : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#FieldValues#", sbUpdateFieldValues.ToString().TrimEnd(','))
                .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));


            JObject result = new JObject();
            var resultRow = await _unitOfWork.Connection.ExecuteAsync(strUpdateSql, tempParamValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", resultRow));

            return result;
        }

        public async Task<JObject> DoDefaultDeleteMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, string sqlMethodContent = "")
        {
            JObject result = new JObject();
            var tempParamValues = paramValues.DeepClone().ToObject<JObject>();
            if (spriteObjectDto.DeleteAudit)
            {
                StringBuilder sbUpdateFieldValues = new StringBuilder();
                var nowTime = DateTime.Now;
                DeleteAuditDelete(sbUpdateFieldValues, nowTime, tempParamValues);

                var strUpdateSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultUpdate : sqlMethodContent)
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#FieldValues#", sbUpdateFieldValues.ToString().TrimEnd(','))
                    .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));

                var resultRow = await _unitOfWork.Connection.ExecuteAsync(strUpdateSql, tempParamValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultRow));
            }
            else
            {
                var resultRow = await _unitOfWork.Connection.ExecuteAsync((string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultDelete : sqlMethodContent).Replace("#TableName#", spriteObjectDto.Name), tempParamValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultRow));
            }

            return result;
        }

        public async Task<JObject> DoDefaultGetMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JArray fields, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            var resultGet = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<dynamic>((string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultGet : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields)), paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JObject.FromObject(resultGet, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoDefaultListMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JArray fields, JToken orderbys, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>((string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultList : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields))
                .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}"), paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoDefaultUpdateWhereMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, string sqlMethodContent = "")
        {
            StringBuilder sbUpdateFieldValues = new StringBuilder();

            foreach (var paramValue in paramValues)
            {
                var field = paramValue.Key;
                if (field != "Id" && spriteObjectDto.ObjectPropertyDtos.Any(r => r.Name.ToLower() == field.ToLower()))
                {
                    sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}=@{field},");
                }
            }

            var tempParamValues = paramValues.DeepClone().ToObject<JObject>();
            var nowTime = DateTime.Now;

            //if (spriteObjectDto.IsTree) // ?不支持树批量修改
            //{
            //    UpdateTree(sbUpdateFieldValues, spriteObjectDto, tempParamValues);
            //}

            if (spriteObjectDto.ModifyAudit)
            {
                UpdateAuditUpdate(sbUpdateFieldValues, nowTime, tempParamValues);
            }

            string strSqlWhere = CreateSqlWhere(sqlWheres, tempParamValues);

            var strUpdateSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultUpdateWhere : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#FieldValues#", sbUpdateFieldValues.ToString().TrimEnd(','))
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));


            JObject result = new JObject();
            var resultRow = await _unitOfWork.Connection.ExecuteAsync(strUpdateSql, tempParamValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", resultRow));

            return result;
        }

        public async Task<JObject> DoDefaultDeleteWhereMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, string sqlMethodContent = "")
        {
            JObject result = new JObject();
            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            if (spriteObjectDto.DeleteAudit)
            {
                StringBuilder sbUpdateFieldValues = new StringBuilder();
                var nowTime = DateTime.Now;

                DeleteAuditDelete(sbUpdateFieldValues, nowTime, paramValues);

                var strUpdateSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultUpdateWhere : sqlMethodContent)
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#FieldValues#", sbUpdateFieldValues.ToString().TrimEnd(','))
                    .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));

                var resultRow = await _unitOfWork.Connection.ExecuteAsync(strUpdateSql, paramValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultRow));
            }
            else
            {
                string strDeleteSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultDeleteWhere : sqlMethodContent)
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));
                var resultRow = await _unitOfWork.Connection.ExecuteAsync(strDeleteSql, paramValues.ToConventionalDotNetObject());
                result.Add(new JProperty("result", resultRow));
            }

            return result;
        }

        public async Task<JObject> DoDefaultGetWhereMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, JArray fields, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);
            var strGetSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultGetWhere : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields));

            var resultGet = await _unitOfWork.Connection.QueryFirstOrDefaultAsync<dynamic>(strGetSql, paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JObject.FromObject(resultGet, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoDefaultListWhereMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, JArray fields, JToken orderbys, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            var strListSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultListWhere : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields))
                .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}");
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strListSql, paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoSqlMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, JArray fields, JToken orderbys, string sqlMethodContent)
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            var strListSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultListWhere : sqlMethodContent)
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere)
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields))
                .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}");
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strListSql, paramValues.ToConventionalDotNetObject());
            result.Add(new JProperty("result", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));

            return result;
        }

        public async Task<JObject> DoDefaultPageListMethodAsync(SpriteObjectDto spriteObjectDto, JObject paramValues, JToken sqlWheres, JArray fields, JToken orderbys, JToken maxResultCount, JToken skipCount, string sqlMethodContent = "")
        {
            JObject result = new JObject();

            if (paramValues == null)
            {
                paramValues = new JObject();
            }
            string strSqlWhere = CreateSqlWhere(sqlWheres, paramValues);

            string strSoftDelete = spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : "";

            var intMaxResultCount = int.MaxValue;
            var intSkipCount = 0;
            if (maxResultCount != null)
            {
                intMaxResultCount = maxResultCount.ToObject<int>();
            }
            if (skipCount != null)
            {
                intSkipCount = skipCount.ToObject<int>();
            }

            var strPageListSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultPageList : sqlMethodContent.Split(';')[0])
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                .Replace("#Fields#", CreateSqlFields(spriteObjectDto, fields))
                .Replace("#OrderBy#", orderbys == null ? "" : $" ORDER BY {orderbys.ToString()}")
                .Replace("#SkipCount#", intSkipCount.ToString())
                .Replace("#MaxResultCount#", intMaxResultCount.ToString());
            var resultList = await _unitOfWork.Connection.QueryAsync<dynamic>(strPageListSql, paramValues.ToConventionalDotNetObject());

            var strCountSql = (string.IsNullOrEmpty(sqlMethodContent) ? SqlDefaultPageCount : sqlMethodContent.Split(';')[1])
                .Replace("#TableName#", spriteObjectDto.Name)
                .Replace("#SqlWhere#", strSqlWhere + (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""));

            var count = await _unitOfWork.Connection.QueryFirstAsync<int>(strCountSql, paramValues.ToConventionalDotNetObject());

            JObject jResut = new JObject();
            jResut.Add(new JProperty("items", JArray.FromObject(resultList, ExpressSqlHelper.CreateCamelCaseJsonSerializer())));
            jResut.Add(new JProperty("count", count));

            result.Add(new JProperty("result", jResut));

            return result;
        }

        private string CreateSqlFields(SpriteObjectDto spriteObjectDto, JArray fields)
        {
            if (fields == null || fields.Count == 0)
            {
                return "*";
            }

            //var tempFields = fields.Where(r => spriteObjectDto.ObjectPropertyDtos.Any(t => t.Name == r.ToString())).ToList();
            //if (tempFields == null || tempFields.Count == 0)
            //{
            //    return "*";
            //}
            //fields = tempFields;

            return string.Join(",", fields.Select(r => $"{MysqlConsts.PreMark}{r}{MysqlConsts.PostMark}"));
        }

        private string CreateSqlWhere(JToken sqlWheres, JObject tempParamValues)
        {
            if (sqlWheres == null)
            {
                return "1=1";
            }
            StringBuilder sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append("1=1");

            ExpressSqlModel expressSqlModel = sqlWheres.ToObject<ExpressSqlModel>();
            if (expressSqlModel != null && expressSqlModel.Children != null && expressSqlModel.Children.Count > 0)
            {
                string strSql = ExpressSqlHelper.CreateSqlWhere(expressSqlModel, tempParamValues, CreateConditionSql);
                if (!string.IsNullOrEmpty(strSql))
                {
                    sbSqlWhere.Append($" AND {strSql}");
                }
            }

            return sbSqlWhere.ToString();
        }

        private string CreateSqlWhereArray(JArray sqlWheres, JObject tempParamValues)
        {
            StringBuilder sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append("1=1");
            if (sqlWheres == null || sqlWheres.Count == 0)
            {
                return sbSqlWhere.ToString();
            }

            int index = 1;
            foreach (var sqlWhere in sqlWheres)
            {
                var conditionType = sqlWhere["conditionType"].ToObject<EConditionType>();
                var field = sqlWhere["field"].ToString();
                switch (conditionType)
                {
                    case EConditionType.等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}=@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.Like:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} LIKE CONCAT('%',@SW{index}_{field},'%')");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.In:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IN @SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.Between:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}  BETWEEN @SW{index}_{field}_1 AND @SW{index}_{field}_2");
                        var inValues = sqlWhere["value"].ToArray();
                        tempParamValues.Add(new JProperty($"SW{index}_{field}_1", inValues[0].ToObject<object>()));
                        tempParamValues.Add(new JProperty($"SW{index}_{field}_2", inValues[1].ToObject<object>()));
                        break;
                    case EConditionType.大于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.大于等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}>=@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.小于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.小于等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<=@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.不等于:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark}<>@SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    case EConditionType.Null:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NULL");
                        break;
                    case EConditionType.NotNull:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} IS NOT NULL");
                        break;
                    case EConditionType.NotIn:
                        sbSqlWhere.Append($" AND {MysqlConsts.PreMark}{field}{MysqlConsts.PostMark} NOT IN @SW{index}_{field}");
                        tempParamValues.Add(new JProperty($"SW{index}_{field}", sqlWhere["value"].ToObject<object>()));
                        break;
                    default:
                        break;
                }

                index++;
            }

            return sbSqlWhere.ToString();
        }

        #region Auto Field Value

        private void CreateTree(StringBuilder sbInsertFields, StringBuilder sbInsertValues, SpriteObjectDto spriteObjectDto, JObject tempParamValues)
        {
            var pid = tempParamValues["pId"];
            sbInsertFields.Append($"{MysqlConsts.PreMark}PId{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}TreeCode{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}Path{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}Title{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}Icon{MysqlConsts.PostMark},");
            sbInsertValues.Append($"@PId,");
            sbInsertValues.Append($"@TreeCode,");
            sbInsertValues.Append($"@Path,");
            sbInsertValues.Append($"@Title,");
            sbInsertValues.Append($"@Icon,");
            if (pid == null || string.IsNullOrEmpty(pid.ToString()))
            {
                // PId,TreeCode,Path,Icon,Title
                object objPId;
                switch (spriteObjectDto.KeyType)
                {
                    case EKeyType.Int:
                        objPId = 0;
                        break;
                    case EKeyType.Guid:
                        objPId = Guid.Empty;
                        break;
                    default:
                        objPId = Guid.Empty;
                        break;
                }
                tempParamValues.Add(new JProperty("PId", objPId));
                tempParamValues.Remove("pId");
                tempParamValues.Add(new JProperty("TreeCode", "0."));
                tempParamValues.Remove("treeCode");
                tempParamValues.Add(new JProperty("Path", tempParamValues["title"]?.ToObject<object>()));
                tempParamValues.Remove("path");
                tempParamValues.Add(new JProperty("Title", tempParamValues["title"]?.ToObject<object>()));
                tempParamValues.Remove("title");
                tempParamValues.Add(new JProperty("Icon", tempParamValues["icon"]?.ToObject<object>()));
                tempParamValues.Remove("icon");
            }
            else
            {
                var resultGet = _unitOfWork.Connection.QueryFirstOrDefault<dynamic>(SqlDefaultGet
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                    .Replace("#Fields#", CreateSqlFields(spriteObjectDto, null)), new { Id = pid?.ToObject<object>() });

                tempParamValues.Add(new JProperty("PId", resultGet.Id));
                tempParamValues.Remove("pId");
                tempParamValues.Add(new JProperty("TreeCode", $"{resultGet.TreeCode}{resultGet.Id}."));
                tempParamValues.Remove("treeCode");
                tempParamValues.Add(new JProperty("Path", $"{resultGet.Path}/{tempParamValues["title"]}"));
                tempParamValues.Remove("path");
                tempParamValues.Add(new JProperty("Title", tempParamValues["title"]?.ToObject<object>()));
                tempParamValues.Remove("title");
                tempParamValues.Add(new JProperty("Icon", tempParamValues["icon"]?.ToObject<object>()));
                tempParamValues.Remove("icon");
            }
        }

        private void CreateAuditCreate(StringBuilder sbInsertFields, StringBuilder sbInsertValues, DateTime nowTime, JObject tempParamValues)
        {
            sbInsertFields.Append($"{MysqlConsts.PreMark}CreatorId{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}CreationTime{MysqlConsts.PostMark},");
            sbInsertValues.Append($"@CreatorId,");
            sbInsertValues.Append($"@CreationTime,");

            tempParamValues.Add(new JProperty("CreatorId", ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId));
            tempParamValues.Remove("creatorId");
            tempParamValues.Add(new JProperty("CreationTime", nowTime));
            tempParamValues.Remove("creationTime");
        }

        private void CreateAuditUpdate(StringBuilder sbInsertFields, StringBuilder sbInsertValues, DateTime nowTime, JObject tempParamValues)
        {
            sbInsertFields.Append($"{MysqlConsts.PreMark}LastModifierId{MysqlConsts.PostMark},");
            sbInsertFields.Append($"{MysqlConsts.PreMark}LastModificationTime{MysqlConsts.PostMark},");
            sbInsertValues.Append($"@LastModifierId,");
            sbInsertValues.Append($"@LastModificationTime,");

            tempParamValues.Add(new JProperty("LastModifierId", ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId));
            tempParamValues.Remove("lastModifierId");
            tempParamValues.Add(new JProperty("LastModificationTime", nowTime));
            tempParamValues.Remove("lastModificationTime");
        }

        private void UpdateAuditUpdate(StringBuilder sbUpdateFieldValues, DateTime nowTime, JObject tempParamValues)
        {
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}LastModifierId{MysqlConsts.PostMark}=@LastModifierId,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}LastModificationTime{MysqlConsts.PostMark}=@LastModificationTime,");

            tempParamValues.Add(new JProperty("LastModifierId", ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId));
            tempParamValues.Remove("lastModifierId");
            tempParamValues.Add(new JProperty("LastModificationTime", nowTime));
            tempParamValues.Remove("lastModificationTime");
        }

        private void UpdateTree(StringBuilder sbUpdateFieldValues, SpriteObjectDto spriteObjectDto, JObject tempParamValues)
        {
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}Title{MysqlConsts.PostMark}=@Title,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}Icon{MysqlConsts.PostMark}=@Icon,");
            tempParamValues.Add(new JProperty("Title", tempParamValues["title"]?.ToObject<object>()));
            tempParamValues.Remove("title");
            tempParamValues.Add(new JProperty("Icon", tempParamValues["icon"]?.ToObject<object>()));
            tempParamValues.Remove("icon");

            var pid = tempParamValues["pId"];
            if (pid == null || string.IsNullOrEmpty(pid.ToString()))
            {
                object objPId;
                switch (spriteObjectDto.KeyType)
                {
                    case EKeyType.Int:
                        objPId = 0;
                        break;
                    case EKeyType.Guid:
                        objPId = Guid.Empty;
                        break;
                    default:
                        objPId = Guid.Empty;
                        break;
                }

                sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}PId{MysqlConsts.PostMark}=@PId,");
                sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}TreeCode{MysqlConsts.PostMark}=@TreeCode,");
                sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}Path{MysqlConsts.PostMark}=@Path,");

                tempParamValues.Add(new JProperty("PId", objPId));
                tempParamValues.Remove("pId");
                tempParamValues.Add(new JProperty("TreeCode", "0."));
                tempParamValues.Remove("treeCode");
                tempParamValues.Add(new JProperty("Path", tempParamValues["title"].ToString()));
                tempParamValues.Remove("path");
                return;
            }

            var id = tempParamValues["id"];
            var dbData = _unitOfWork.Connection.QueryFirstOrDefault<dynamic>(SqlDefaultGet
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                    .Replace("#Fields#", CreateSqlFields(spriteObjectDto, null)), new { Id = id?.ToObject<object>() });

            var dbParentData = _unitOfWork.Connection.QueryFirstOrDefault<dynamic>(SqlDefaultGet
                    .Replace("#TableName#", spriteObjectDto.Name)
                    .Replace("#SqlWhere#", (spriteObjectDto.DeleteAudit ? " AND IsDeleted=0" : ""))
                    .Replace("#Fields#", CreateSqlFields(spriteObjectDto, null)), new { Id = pid?.ToObject<object>() });

            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}Path{MysqlConsts.PostMark}=@Path,");
            tempParamValues.Add(new JProperty("Path", $"{dbParentData.Path}/{tempParamValues["title"]}"));
            tempParamValues.Remove("path");
            if (Convert.ToString(dbData.PId) == pid.ToString()) // 未修改PId
            {
                return;
            }

            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}PId{MysqlConsts.PostMark}=@PId,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}TreeCode{MysqlConsts.PostMark}=@TreeCode,");

            tempParamValues.Add(new JProperty("PId", dbParentData.Id));
            tempParamValues.Remove("pId");
            tempParamValues.Add(new JProperty("TreeCode", $"{dbParentData.TreeCode}{dbParentData.Id}."));
            tempParamValues.Remove("treeCode");
        }

        private void DeleteAuditDelete(StringBuilder sbUpdateFieldValues, DateTime nowTime, JObject tempParamValues)
        {
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}DeleterId{MysqlConsts.PostMark}=@DeleterId,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}DeletionTime{MysqlConsts.PostMark}=@DeletionTime,");
            sbUpdateFieldValues.Append($"{MysqlConsts.PreMark}IsDeleted{MysqlConsts.PostMark}=@IsDeleted,");

            tempParamValues.Add(new JProperty("DeleterId", ServiceLocator.ServiceProvider.GetService<ICurrentUser>().UserId));
            tempParamValues.Remove("deleterId");
            tempParamValues.Add(new JProperty("DeletionTime", nowTime));
            tempParamValues.Remove("deletionTime");
            tempParamValues.Add(new JProperty("IsDeleted", true));
            tempParamValues.Remove("isDeleted");
        }

        #endregion

        #endregion
    }
}
